/*
 * Copyright 2016 Wealth Inc. All rights reserved.
 */
 package com.wealth.commodity.repository;

import java.util.List;

import javax.persistence.criteria.CriteriaBuilder.In;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.wealth.commodity.entity.Goods;

import iaas.repository.BaseRepository;

/**
 * @author chan
 * @version 1.0
 * @since 1.0
 */
 
@Repository
public interface GoodsDao extends BaseRepository<Goods, Long> ,JpaSpecificationExecutor<Goods>{

	
	/**
	 * 查询某分类下状态为上架的商品
	 * @param categoryId
	 * @param status
	 * @return
	 */
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND b.market_id=?3 and b.type=0 AND a.STATUS =?2 AND a.category_id=?1  ORDER BY a.id  LIMIT 20",nativeQuery=true)
	public List<Goods> getPag (Long categoryId , Integer status,Long marketId);
	
	/**
	 * 查询特殊线下商品
	 * @param status
	 * @return
	 */
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND  b.type=?4  and  b.market_id=?2   AND a.STATUS =?1 and a.id>?3  ORDER BY a.id  LIMIT 20",nativeQuery=true)
	public List<Goods> getDownPag (Integer status,Long marketId,Integer id,Integer type);
	/**
	 * 查询某分类下状态为上架的商品(加载更多)
	 * @param categoryId
	 * @param status
	 * @return
	 */
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND  b.type=0  and b.market_id=?4  AND a.STATUS =?3 AND a.category_id=?2 and a.id>?1  ORDER BY a.id LIMIT 20",nativeQuery=true)
	public List<Goods> getPagMore (Long id,Long categoryId , Integer status,Long marketId);

	/** 惠生活猜你喜欢，查询惠生活的销量前5商品，但是不包含首页特别商户 30创建的商品 */
	//市场+状态
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND b.market_id=?  AND a.STATUS =? and b.status=1 and b.id!=30  ORDER BY a.sales DESC LIMIT 20",nativeQuery=true)
	public List<Goods> findByMerchantMarketIdAndStatusOrderBySalesDesc (Long marketid, Integer status);
	
	//商户+状态
	public List<Goods> findByMerchantIdAndStatusOrderBySalesDesc (Long merchantid, Integer status);

	//市场+状态(用于品牌购和本地购主页面)
	public List<Goods> findTop12ByCategoryIdAndStatusOrderBySalesDesc(Long categoryId, Integer status);

	//条件查询商品
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND b.market_id=?4  and b.type=0 AND a.STATUS =?1 AND a.category_id=?2 and a.name like ?3  ORDER BY a.id  LIMIT 20",nativeQuery=true)
	public List<Goods> findByStatusAndCategoryIdAndNameLike(Integer status,Long categoryId,String name,Long marketId);
	
	//条件查询商品(主页面模糊查询商品)
	@Query(value="SELECT * FROM commodity_goods a,t_merchant_merchant b WHERE a.merchant_id = b.id AND b.market_id=?3 and b.type=0  AND a.STATUS =?1  and a.name like ?2 and a.id>?4  ORDER BY a.id  LIMIT 20",nativeQuery=true)
	public List<Goods> findByStatusAndNameLike(Integer status,String name,Long marketId,Long Id);
		
	
	
	//惠生活模糊查询商品
	public List<Goods> findTop5ByStatusAndMerchantStatusAndMerchantMarketIdAndNameLikeOrderBySalesDesc(Integer  status,Integer mstatus,Long  marketId,String name);
	
	//查询特价商品
	@Query(value="SELECT * FROM commodity_goods WHERE id>?1 AND is_cheap=?2  AND STATUS =?3 ORDER BY id   LIMIT 20",nativeQuery=true)
	public List<Goods> findCheapGoods(Long id,Integer ischeap,Integer status);
	
	//查询折扣商品
	@Query(value="SELECT * FROM commodity_goods WHERE id>?1 AND is_discount=?2  AND STATUS =?3 ORDER BY id  LIMIT 20",nativeQuery=true)
	public List<Goods> findDiscountGoods(Long id, Integer ischeap,Integer status);
	
	
	//查询满立减商品
	@Query(value="SELECT * FROM commodity_goods WHERE id>?1 AND is_fullcut=?2  AND STATUS =?3  And merchant_id=?4 ORDER BY id  LIMIT 20",nativeQuery=true)
	public List<Goods> findFullcutGoods(Long id, Integer ischeap,Integer status,Long merchantId);
	
	//查询商品的虚拟规则
	public Goods findByGoodsVirtualId(Long goodsVirtualId);
	
	//查询某商户的打折商品
	@Query(value="SELECT * FROM commodity_goods WHERE id>?1  And merchant_id=?2 AND is_discount=?3 AND STATUS =?4 ORDER BY id LIMIT 20 ",nativeQuery=true)
	public List<Goods> findByMerchantIdAndIsDiscountAndStatus(Long id,Long merchantId,Integer isDiscount,Integer status);
	
	
	/**查询所有商品*/
	public List<Goods> findByStatus(Integer status);
	
	
}
